﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using System.Linq;

using System.Data.Sql;


/// <summary>
/// Summary description for DistributerAC
/// </summary>
/// 
namespace Bestlife.ERP.DataAccess
{
    public class DistributerAC:System.Web.UI.Page
    {
        string returncomm;
        string returnid;
        public DistributerAC()
        {
            //
            //
            //
        }
        public static Bestlife.ERP.Entities.E_Profile getallprofileinfo(string id)
        {

            Bestlife.ERP.Entities.E_Profile pro = new Entities.E_Profile();
            Bestlife.ERP.DataAccess.users user = new users();
            Bestlife.ERP.Entities.E_Bonus bonus = new Entities.E_Bonus();
            try
            {
                //Get the userID using the users table
                string uid = user.GetUserId(id);

                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);
                //Select Distributor Details using userid
                String Ssql = "SELECT * from Tbl_Distributor where Dis_ID ='" + uid + "'";
                
                SqlDataReader rdR;
                rdR = clsCon.ReadTable(Ssql);
                if(rdR.Read())
                {
                    pro.Title1 = rdR["Title"].ToString();
                    pro.Name1 = rdR["Giv_Name"].ToString();
                // pro.Rank1 = rdR["user_Status"].ToString();
                //pro.Aboutme1 = rdR[2].ToString();
                    pro.Contact1 = rdR["Mob_No"].ToString();
                    pro.URL1 = "www."+rdR["Web_Url"].ToString()+".bestlife.lk";
                    pro.Email1 = rdR["Email"].ToString();
                    pro.Address11 = rdR["Address1"].ToString();
                    pro.Address21 = rdR["Address2"].ToString();
                    pro.Address31 = rdR["City"].ToString();
                
                rdR.Close();
                }
                //Get the rank using bonus table with its user id
                String Bsql = "SELECT * from Bonus where User_id='" + uid + "'";
                SqlDataReader reader2;
                reader2 = clsCon.ReadTable(Bsql);
                if (reader2.Read())
                {
                    pro.Rank1 = reader2["user_Status"].ToString();
                    pro.PPV1 = reader2["ppv"].ToString();
                    pro.CPV1 = reader2["Cpv"].ToString();
                    reader2.Close();
                }
                
                
            }
            catch (Exception)
            {
                
                
                
            }
            return pro;
        }
        public  string historycheck(string id,string month,string year)
        {
            string datepch = "";
            try
            {
                int m = int.Parse(month);
                if(m<10) 
                {
                     datepch = year + "-" +"0"+month;
                } 
                
                else 
                {
                     datepch = year + "-" + month;
                }
               
                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);
                //Select Distributor Details using userid
            //    String Ssql = "SELECT[User_id],[H_Tpv],[H_Cpv],[H_T_Commission],[H_Tbv],[Date_Create] FROM [BestlifeDB].[dbo].[History_Bonus] where [User_id]='" + id + "'and ' [Date_Create] LIKE '" + datepch + "%'";
                String Ssql = "SELECT[User_id],[H_Tpv],[H_Cpv],[H_T_Commission],[H_Tbv],[Date_Create] FROM [BestlifeDB].[dbo].[History_Bonus] where [User_id]='" + id + "'and [Date_Create] LIKE '" + datepch +"%'";
                SqlDataReader rdR;
                rdR = clsCon.ReadTable(Ssql);
                if (rdR.Read())
                {
                    returnid = rdR["H_T_Commission"].ToString();
                    rdR.Close();
                }
                else returnid = "0";
            }
            catch(Exception)
            {
            }
            return returnid;
        }

        public string bvcheck(string id, string month, string year)
        {
            string datepch = "";
            try     
            {          
                int m = int.Parse(month);
                if (m < 10)
                {
                    datepch = year + "-" + "0" + month;
                }

                else
                {
                    datepch = year + "-" + month;
                }
             
                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);
                //Select Distributor Details using userid
                //    String Ssql = "SELECT[User_id],[H_Tpv],[H_Cpv],[H_T_Commission],[H_Tbv],[Date_Create] FROM [BestlifeDB].[dbo].[History_Bonus] where [User_id]='" + id + "'and ' [Date_Create] LIKE '" + datepch + "%'";
                String Ssql = "SELECT[User_id],[H_Tpv],[H_Cpv],[H_T_Commission],[H_Tbv],[Date_Create] FROM [BestlifeDB].[dbo].[History_Bonus] where [User_id]='" + id + "'and [Date_Create] LIKE '" + datepch + "%'";
                SqlDataReader rdR;
                rdR = clsCon.ReadTable(Ssql);
                if (rdR.Read())
                {
                    returnid = rdR["H_Tbv"].ToString();
                    rdR.Close();
                }
                else returnid = "0";
            }
            catch (Exception)
            {
            }
            return returnid;
        }


        public string Dplan(string DID) 
        {
            string st = "";
            
                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);
                //Select Distributor Details using userid
                String Ssql = "SELECT Dplan from Tbl_Distributor where Dis_ID ='" + DID + "'";
                
                SqlDataReader rdR;
                rdR = clsCon.ReadTable(Ssql);
                if (rdR.Read())
                {
                   
                    st= rdR["Dplan"].ToString();
                }
                rdR.Close();

                return st;
        }

        public List<Bestlife.ERP.Entities.E_Chart> chart(string userid,string year)
        {
            List<Bestlife.ERP.Entities.E_Chart> chart_return = new List<Entities.E_Chart>();
            chart_return.Clear();
           
            try
            {
                String connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                Bestlife.ERP.DataAccess.clsCon.connect(connStr);
                //String Ssql = "SELECT	[User_id],SUM (H_T_Commission) AS TOT_COM FROM [BestlifeDB].[dbo].[History_Bonus] WHERE USER_ID='" + userid + "' AND H_Year='"+year+"' group by User_id";
                String Ssql = "SELECT [H_Month],SUM(H_T_Commission) AS TotCom FROM [dbo].[History_Bonus] WHERE USER_ID='" + userid + "' AND  H_Year='" + year + "'  GROUP BY  History_Bonus.H_Month";
                SqlDataReader rdR;
                rdR = clsCon.ReadTable(Ssql);
                while (rdR.Read())
                {
                    Bestlife.ERP.Entities.E_Chart chart_D = new Entities.E_Chart();
                    chart_D.H_month = rdR["H_Month"].ToString();
                    chart_D.H_comm = decimal.Parse(rdR["TotCom"].ToString());
                    chart_return.Add(chart_D); 
                }
                rdR.Close();
            }
            catch (Exception)
            {
            }
            return chart_return;
        }


    }
    
}